package o2o.web.actions.report;

import com.jinyou.utils.common.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONObject;
import o2o.cache.order.OrderGoodsLabelMemory;
import o2o.cache.order.OrderInfoImageMemory;
import o2o.cache.order.OrderInfoMemory;
import o2o.cache.order.OrderPayMemory;
import o2o.cache.postman.CityPostmanMemory;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.dao.UserDaoSql;
import o2o.config.dao.utils.DaoUtils;
import o2o.config.dao.utils.Expression;
import o2o.config.dao.utils.QueryCondition;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.order.ORDER_TYPE;
import o2o.dao.PO.order.OrderGoodsLabel;
import o2o.dao.PO.order.OrderInfo;
import o2o.dao.PO.order.OrderPayInfo;
import o2o.postman.model.CityPostman;
import o2o.dao.PO.postman.POSTMAN_VERIFY_STATUS;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.PO.wallet.PAY_TYPE;
import o2o.dao.PO.wallet.UserWalletPop;
import o2o.dao.PO.wallet.UserWalletPopReport;
import o2o.dao.VO.order.OrderInfoVO;
import o2o.dao.VO.postman.CityPostmanVO;
import o2o.dao.VO.postman.CityPostmanVerifyVO;
import o2o.dao.VO.shop.ShopInfoVO;
import o2o.dao.VO.user.UserInfoVO;
import o2o.dao.VO.user.UserSignInfoVO;
import o2o.dao.VO.wallet.UserWalletPopVO;
import o2o.dao.VO.wallet.UserWalletVO;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.web.actions.user.User;
import org.apache.commons.io.FileUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import pay.ChannelUtils;

import java.io.File;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.jinyou.utils.common.ValidateUtil.isAbsInteger;

/**
 * Created by lmy on 2018/1/29 0029.
 */
@Controller
public class ReportAction {

    //管理后台导出Excel订单列表
    @RequestMapping(method = RequestMethod.POST, value = "/admin/Wallet/pop/export")
    @ResponseBody
    public String pop_list(String token, String username, Integer isDone, String startTime, String endTime) {
        //        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        //验证是否有登录权限
        if (!User.getInstance().hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        StringBuffer sb = new StringBuffer("SELECT a.*,b.ownUsername,b.address,b.shopName, b.telephone FROM ").append(PojoTables.USER_WALLET_POP).append(" a ")
                .append(" left join ").append(PojoTables.ShopInfo).append(" b on a.username = b.ownUsername ")
                .append(" where b.id is not null and b.delFlag = 0 and a.delFlag =0 ");
        //isDone  0所有 1已提现  2所有未提现
        if (isAbsInteger(isDone) && isDone > 0) {
            if (0 == 2 - isDone) {
                sb.append(" and a.isDone=0 ");
            } else {
                sb.append(" and a.isDone=1 ");
            }
        }
        if (ValidateUtil.isNotNull(username)) {
            sb.append(" and a.username='").append(username).append("'");
        }
        List<UserWalletPopReport> list = null;
        try {
            list = (List<UserWalletPopReport>) UserDaoSql.getInstance().list(UserWalletPopReport.class, sb.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("查询失败，请稍后再试");
        }
//        String filehash = null;
////        String fileName = System.currentTimeMillis() + "";
////        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
////        Date date = new Date(System.currentTimeMillis());
////        String fileName = formatter.format(date);
//        try {
//            filehash = generateWalletPopExcel(list);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }

        String downloadUrl = generateWalletPopExcel(list);
        if (ValidateUtil.isNotNull(downloadUrl)) {
            return AjaxResult.successResult(SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl);
        }

        return AjaxResult.successResult();
    }


    //---------------------------------生成Excel
    private String generateWalletPopExcel(List<UserWalletPopReport> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {

//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.WALLET_POP + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "名称"));
            sheet.addCell(new Label(col++, row, "电话"));
            sheet.addCell(new Label(col++, row, "地址"));
            sheet.addCell(new Label(col++, row, "本次提现金额"));
            sheet.addCell(new Label(col++, row, "提现渠道"));      //  ?
            sheet.addCell(new Label(col++, row, "微信姓名"));
            sheet.addCell(new Label(col++, row, "提现微信账号"));
            sheet.addCell(new Label(col++, row, "支付宝姓名"));
            sheet.addCell(new Label(col++, row, "提现支付宝账号"));
            sheet.addCell(new Label(col++, row, "申请时间"));
            sheet.addCell(new Label(col++, row, "是否提现成功"));
            sheet.addCell(new Label(col++, row, "确定提现时间"));
            row = 1;
            for (UserWalletPopReport record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String time = sdf.format(record.getCreateTime()); // 申请时间
                String poptime = "";
                if (record.getIsDone() != 0) {
                    poptime = sdf.format(record.getPopTime()); // 提现时间
                } else {
                    poptime = "";
                }

                String channel = "";
                String isDone = "";
//                if (record.getChannel().equals("alipay")) {
//                    channel = "支付宝";
//                } else {
//                    channel = "微信";
//                }
                if (ValidateUtil.isNotNull(record.getChannel())) {
                    switch (record.getChannel()) {
                        case PAY_TYPE.ALLIPAY: {
                            channel = "支付宝";
                        }
                        break;
                        case PAY_TYPE.WX: {
                            channel = "微信";
                        }
                        break;
                        case PAY_TYPE.BANK_CARD: {
                            channel = "银行卡";
                        }
                        break;
                    }
                }

                if (record.getIsDone() == 0) {
                    isDone = "未成功";
                } else {
                    isDone = "成功";
                }

                sheet.addCell(new Label(col++, row, record.getShopName()));
                sheet.addCell(new Label(col++, row, record.getTelephone()));
                sheet.addCell(new Label(col++, row, record.getAddress()));
                sheet.addCell(new Number(col++, row, record.getMoney()));
                sheet.addCell(new Label(col++, row, channel));      //  ?
//            if (record.getType() == 1) {
//                sheet.addCell(new Number(col++, row, record.getMoney(), wcfN));
//                sheet.addCell(new Label(col++, row, ""));
//            } else {
//                sheet.addCell(new Label(col++, row, ""));
//                sheet.addCell(new Number(col++, row, record.getMoney(), wcfN));
//            }
                sheet.addCell(new Label(col++, row, record.getTenpayRealName(), wcfN));    //   shisha
                sheet.addCell(new Label(col++, row, record.getTenpay()));
                sheet.addCell(new Label(col++, row, record.getAlipayRealName()));
                sheet.addCell(new Label(col++, row, record.getAlipay()));
                sheet.addCell(new Label(col++, row, time));
                sheet.addCell(new Label(col++, row, isDone));     //  ?
                sheet.addCell(new Label(col++, row, poptime));

                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    //管理后台导出Excel订单列表（按城市获取）
    @RequestMapping(method = RequestMethod.POST, value = "/admin/Order/export")
    @ResponseBody
    public String order_list(String token,
                             String city, String county,
                             String shopUsername, String shopName,//店铺信息
                             String name, String phone, String address, //购买人信息
                             String orderNo, //订单编号
                             @RequestParam(defaultValue = "-1") Integer isZiQu, // 取货方式(-1全部 0配送 1自取 2到店堂食 3精选直邮)
                             Integer orderType, // 订单类型
                             Integer isSquareOrder,   //  是否是创业商圈订单
                             Integer orderStatus,//订单状态
                             Long shopId,//店铺ID
                             Long startTime, Long endTime,
                             Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        //验证用户是否有权限
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (orderType == null) {
            orderType = 0;
        }

        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }

        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 10;
        }
        List<OrderInfo> list = new ArrayList<>();

        //不按店铺查
        StringBuffer sb = new StringBuffer();
        //数据库中查出订单编号  再去缓存取内容
        if (ValidateUtil.isNotNull(shopUsername) || ValidateUtil.isNotNull(shopName)) {
            sb.append(" from ").append(PojoTables.OrderInfo).append(" a ")
                    .append(" left join city_agent cag on cag.id = a.agentId ")
                    .append(" left join ").append(PojoTables.ShopInfo)
                    .append(" b on a.shopId = b.id where b.id is not null and b.delFlag = 0 and a.delFlag = 0 and a.shopId > 0 ");
//                    .append(" and ( 1=1 ");
            if (user.getUserType() == UserType.AGENT_MANAGER) {
                sb.append(" and cag.agentManagerUsername=").append(user.getUsername()).append(" ");
            }
            if (ValidateUtil.isNotNull(shopUsername)) {
                sb.append(" and b.shopName like '%").append(shopUsername).append("%' ");
            }
            if (ValidateUtil.isNotNull(shopName)) {
                sb.append(" and b.shopName like '%").append(shopName).append("%' ");
            }
            if (orderType != 0) {
                sb.append(" and a.orderType = ").append(orderType).append(" ");
            }
            if (ValidateUtil.isNotNull(name)) {
                sb.append(" and a.buyer like '%").append(name).append("%' ");
            }
            if (ValidateUtil.isNotNull(phone)) {
                sb.append(" and a.telephone like '%").append(phone).append("%' ");
            }
            if (ValidateUtil.isNotNull(address)) {
                sb.append(" and ( a.address like '%").append(address).append("%' ");
                sb.append(" or a.address2 like '%").append(address).append("%' )");
            }
            if (ValidateUtil.isNotNull(orderNo)) {
                sb.append(" and a.orderNo like '%").append(orderNo).append("%' ");
            }


            if (null != orderStatus && orderStatus > 0) {
                sb.append(" and a.orderStatus = ").append(orderStatus);
            }

            if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
                sb.append(" and a.createTime Between ").append(startTime).append(" and ").append(endTime);
            }

            if (ValidateUtil.isNotNull(city)) {
                sb.append(" and cag.city = '").append(city).append("'");
            }
            if (ValidateUtil.isNotNull(county)) {
                sb.append(" and b.county = '").append(county).append("'");
            }
            if (null != isZiQu && isZiQu != -1) {
                sb.append(" and a.isZiQu = ").append(isZiQu).append(" ");
            }
        } else {

            String lefts = "";
            String isBid = "";
            if (orderType == 1) {
                lefts = " left join " + PojoTables.ShopInfo + " b on a.shopId = b.id";
                isBid = " and b.id is not null and b.delFlag = 0 and a.shopId > 0 ";
            }
            sb.append(" from ").append(PojoTables.OrderInfo).append(" a ")
                    .append(" left join city_agent cag on cag.id = a.agentId ")
                    .append(lefts)
                    .append(" where a.delFlag = 0 ").append(isBid);

//            sb.append(" from ").append(PojoTables.OrderInfo).append(" a left join ").append(PojoTables.ShopInfo)
//                    .append(" b on a.shopId = b.id where b.id is not null and a.delFlag = 0 and b.delFlag = 0 and a.shopId > 0 ");
//                    .append(" and ( 1=1 ");
            if (user.getUserType() == UserType.AGENT_MANAGER) {
                sb.append(" and cag.agentManagerUsername=").append(user.getUsername()).append(" ");
            }
            if (ValidateUtil.isNotNull(name)) {
                sb.append(" and a.buyer like '%").append(name).append("%' ");
            }
            if (ValidateUtil.isNotNull(phone)) {
                sb.append(" and a.telephone like '%").append(phone).append("%' ");
            }
            if (orderType != 0) {
                sb.append(" and a.orderType = ").append(orderType).append(" ");
            }
            if (ValidateUtil.isNotNull(address)) {
                sb.append(" and ( a.address like '%").append(address).append("%' ");
                sb.append(" or a.address2 like '%").append(address).append("%' ) ");
            }
            if (ValidateUtil.isNotNull(orderNo)) {
                sb.append(" and a.orderNo like '%").append(orderNo).append("%' ");
            }
//            sb.append(") ");
            if (null != orderStatus && orderStatus > 0) {
                sb.append(" and a.orderStatus = ").append(orderStatus);
            }

            if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
                sb.append(" and a.createTime Between ").append(startTime).append(" and ").append(endTime);
            }
            if (ValidateUtil.isNotNull(city)) {
                sb.append(" and cag.city = '").append(city).append("'");
            }
            if (ValidateUtil.isNotNull(county)) {
                if (orderType == 1) {
                    sb.append(" and b.county = '").append(county).append("'");
                } else {
                    sb.append(" and cag.county = '").append(county).append("'");
                }
            }
            if (ValidateUtil.isAbsLong(shopId)) {
                sb.append(" and a.shopId= '").append(shopId).append("'");
            }
            if (null != isZiQu && isZiQu != -1) {
                sb.append(" and a.isZiQu = ").append(isZiQu).append(" ");
            }
        }
        if (isSquareOrder != null && isSquareOrder == 1) {
            sb.append(" and a.orderNo like 'P%'");
        }
        Long totalCount = 0l;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select count(a.orderNo) as counts ");
            sbSql.append(sb.toString());
            totalCount = UserDaoSql.getInstance().getCount(sbSql.toString());
//            System.out.println(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            totalCount = 0l;
        }


        List<HashMap<String, Object>> listMap = null;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select  a.orderNo ");
            sbSql.append(sb.toString());
//            sbSql.append(" limit ").append((page - 1) * size).append(",").append(size);
            listMap = UserDaoSql.getInstance().queryBySql(sbSql.toString());
//            System.out.println(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            listMap = null;
        }
        if (ValidateUtil.isAbsList(listMap)) {
            for (int i = 0; i < listMap.size(); i++) {
                if (null == listMap.get(i)) {
                    continue;
                }
                String no = (String) listMap.get(i).get("orderNo");
                if (ValidateUtil.isNull(no)) {
                    continue;
                }
                OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(no);
                if (null == orderInfo) {
                    continue;
                }
                list.add(orderInfo);
            }
        }
        boolean isDownOrderDis = false;
        if (SYS_SETTINGS.ORDER_EXPORT_IS_ADD_SHARE == 1) {  // 是否导出分成信息
            isDownOrderDis = true;
        }
        Map<String, OrderPayInfo> orderPayMap = new HashMap<>();
        if (ValidateUtil.isAbsList(list)) {
            List<OrderInfoVO> voList = new ArrayList<OrderInfoVO>();
            for (int i = 0; i < list.size(); i++) {
                if (null == list.get(i)) {
                    continue;
                }
                OrderInfoVO orderInfoVO = new OrderInfoVO();
                BeanUtils.copyProperties(list.get(i), orderInfoVO);
                // try {
                //     ObjectUtils.copyPropertiesExclude(list.get(i), orderInfoVO, null);
                // } catch (Exception e) {
                //     e.printStackTrace();
                //     continue;
                // }
                // if (null == orderInfoVO) {
                //     continue;
                // }
                if (ValidateUtil.isNotNetUrl(orderInfoVO.getShopImageUrl())) {
                    orderInfoVO.setShopImageUrl(SYS_CONFIG_PATH.IMAGE_BASE_URL + orderInfoVO.getShopImageUrl());
                }
                if (isDownOrderDis) {
                    OrderPayInfo payInfo = OrderPayMemory.getInstance().get(orderInfoVO.getOrderNo());
                    if (payInfo != null) {
                        orderPayMap.put(payInfo.getOrderNo(), payInfo);
                    }
                }
                voList.add(orderInfoVO);
            }

            String downloadUrl = generateOrderExcel(voList, isDownOrderDis ? orderPayMap : null);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                if (ValidateUtil.isNotNetUrl(downloadUrl)) {
                    downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                }
                return AjaxResult.successResult(downloadUrl);
            }
        }
        return AjaxResult.successResult();
    }

    //订单查询V2 管理员（按代理获取订单）
    @RequestMapping(method = RequestMethod.POST, value = "/admin/order/search/list/v2/export")
    @ResponseBody
    public String order_admin_list_v2(String token,
                                      String city, Integer orderType,
                                      String shopUsername, String shopName,
                                      String name, String phone, String address, //购买人信息
                                      String orderNo, //订单编号
                                      Integer orderStatus,//订单状态
                                      Integer isSquareOrder,   //  是否是创业商圈订单
                                      @RequestParam(defaultValue = "-1") Integer isZiQu, // 取货方式(-1全部 0配送 1自取 2到店堂食 3精选直邮)
                                      Long shopId,//店铺ID
                                      Long startTime, Long endTime,
                                      Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        //验证用户是否有权限
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (orderType == null) {
            orderType = 0;
        }

        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }

        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 10;
        }

        // 订单类型
        String orderTypeStr = "";
        String isShopId = "";
        if (ValidateUtil.isAbsInteger(orderType)) {
            orderTypeStr = " and a.orderType=" + orderType;
            if (orderType == 1) {
                isShopId = " and a.shopId > 0 ";
            }
        }
        // 城市
        String cityStr = "";
        if (ValidateUtil.isNotNull(city)) {
            cityStr = " and a.shopCity='" + city + "'";
        }

        // 店铺信息
        String shopIdStr = "";
        String shopNameStr = "";

        if (ValidateUtil.isAbsLong(shopId)) {
            shopIdStr = " and a.shopId='" + shopId + "'";
        }
        if (ValidateUtil.isNotNull(shopName)) {
            shopNameStr = " and a.shopName like '%" + shopName + "%'";
        }

        // 购买人信息
        String nameStr = "";
        String phoneStr = "";
        String addressStr = "";
        String orderNoStr = "";
        String orderStatusStr = "";
        String timeStr = "";
        if (ValidateUtil.isNotNull(name)) {
            nameStr = " and a.buyer like '%" + name + "%'";
        }
        if (ValidateUtil.isNotNull(phone)) {
            phoneStr = " and a.telephone like '%" + phone + "%'";
        }
        if (ValidateUtil.isNotNull(address)) {
            addressStr = " and ( a.address like '%" + address + "%' or a.address2 like '%" + address + "%' )";
        }
        if (ValidateUtil.isNotNull(orderNo)) {
            orderNoStr = " and a.orderNo like '%" + orderNo + "%'";
        }
        if (ValidateUtil.isAbsInteger(orderStatus)) {
            orderStatusStr = " and a.orderStatus = " + orderStatus;
        }
        if (isSquareOrder != null && isSquareOrder == 1) {
            orderStatusStr += " and a.orderNo like 'P%'";
        }
        if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
            timeStr = " and a.createTime Between  " + startTime + " and " + endTime;
        }
        String ziQu = "";
        if (null != isZiQu && isZiQu != -1) {
            ziQu = " and a.isZiQu = " + isZiQu + " ";
        }
        StringBuffer sb = new StringBuffer();
        sb.append(" from ").append(PojoTables.OrderInfo).append(" a ")
                .append(" LEFT JOIN user_agent_relation c ON c.agentId = a.agentId")
                .append(" where c.id IS NOT NULL AND a.delFlag = 0  AND c.delFlag =0 and c.username ='").append(user.getUsername()).append("'")
                .append(cityStr).append(orderTypeStr).append(isShopId).append(shopIdStr).append(shopNameStr).append(nameStr)
                .append(phoneStr).append(addressStr).append(orderNoStr).append(orderStatusStr).append(timeStr).append(ziQu)
                .append(" ORDER BY a.createTime DESC ");
        Long totalCount = 0l;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select count(a.orderNo) as counts ");
            sbSql.append(sb.toString());
            totalCount = UserDaoSql.getInstance().getCount(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            totalCount = 0l;
        }

        List<OrderInfo> list = new ArrayList<>();
        List<HashMap<String, Object>> listMap = null;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select  a.orderNo ");
            sbSql.append(sb.toString());
//            sbSql.append(" limit ").append((page - 1) * size).append(",").append(size);
            listMap = UserDaoSql.getInstance().queryBySql(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            listMap = null;
        }
        if (ValidateUtil.isAbsList(listMap)) {
            for (int i = 0; i < listMap.size(); i++) {
                if (null == listMap.get(i)) {
                    continue;
                }
                String no = (String) listMap.get(i).get("orderNo");
                if (ValidateUtil.isNull(no)) {
                    continue;
                }
                OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(no);
                if (null == orderInfo) {
                    continue;
                }
                list.add(orderInfo);
            }
        }
        boolean isDownOrderDis = false;
        if (SYS_SETTINGS.ORDER_EXPORT_IS_ADD_SHARE == 1) {  // 是否导出分成信息
            isDownOrderDis = true;
        }
        Map<String, OrderPayInfo> orderPayMap = new HashMap<>();

        if (ValidateUtil.isAbsList(list)) {
            List<OrderInfoVO> voList = new ArrayList<OrderInfoVO>();
            for (int i = 0; i < list.size(); i++) {
                if (null == list.get(i)) {
                    continue;
                }
                OrderInfoVO orderInfoVO = new OrderInfoVO();
                try {
                    ObjectUtils.copyPropertiesExclude(list.get(i), orderInfoVO, null);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                if (null == orderInfoVO) {
                    continue;
                }
                if (ValidateUtil.isNotNetUrl(orderInfoVO.getShopImageUrl())) {
                    orderInfoVO.setShopImageUrl(SYS_CONFIG_PATH.IMAGE_BASE_URL + orderInfoVO.getShopImageUrl());
                }
                if (isDownOrderDis) {
                    OrderPayInfo payInfo = OrderPayMemory.getInstance().get(orderInfoVO.getOrderNo());
                    if (payInfo != null) {
                        orderPayMap.put(payInfo.getOrderNo(), payInfo);
                    }
                }

                voList.add(orderInfoVO);
            }
            String downloadUrl = generateOrderExcel(voList, isDownOrderDis ? orderPayMap : null);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
        }
        return AjaxResult.successResult();
    }


    //---------------------------------生成Excel
    public static String generateOrderExcel(List<OrderInfoVO> list, Map<String, OrderPayInfo> payInfoMap) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "订单编号"));
            sheet.addCell(new Label(col++, row, "订单类型"));
            sheet.addCell(new Label(col++, row, "订单创建时间"));
            sheet.addCell(new Label(col++, row, "订单状态"));
            sheet.addCell(new Label(col++, row, "店铺名称"));
            sheet.addCell(new Label(col++, row, "店铺地址"));
            sheet.addCell(new Label(col++, row, "店铺电话"));
            sheet.addCell(new Label(col++, row, "配送员姓名"));
            sheet.addCell(new Label(col++, row, "配送员电话"));
            sheet.addCell(new Label(col++, row, "配送距离(km)"));
            sheet.addCell(new Label(col++, row, "配送费"));
            sheet.addCell(new Label(col++, row, "配送完成时间"));
            sheet.addCell(new Label(col++, row, "收货人"));
            sheet.addCell(new Label(col++, row, "收货人电话"));
            sheet.addCell(new Label(col++, row, "收货地址"));
            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "配送费"));
            sheet.addCell(new Label(col++, row, "优惠配送费（店铺）"));
            sheet.addCell(new Label(col++, row, "打包费"));
            sheet.addCell(new Label(col++, row, "支付金额"));
            sheet.addCell(new Label(col++, row, "支付方式"));
            if (payInfoMap != null) {
                sheet.addCell(new Label(col++, row, "平台优惠"));
                sheet.addCell(new Label(col++, row, "店铺优惠"));
                sheet.addCell(new Label(col++, row, "平台收入"));
                sheet.addCell(new Label(col++, row, "店铺收入"));
                sheet.addCell(new Label(col++, row, "骑手收入"));
            }
            row = 1;
            for (OrderInfoVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String time = sdf.format(record.getCreateTime()); // 申请时间
                String postmanTime = sdf.format(record.getPostmanSureTime()); // 配送完成时间
                double distance = LocationUtil.getDistance(record.getLat(), record.getLng(), record.getShopLat(), record.getShopLng());
                double totalPrice = 0;
                if (record.getIsPaid() == 1) {
                    totalPrice = record.getTotalPrice();
                }
                String channel = ChannelUtils.getChannelName(record.getPayType());
//                if (null != record.getPayType()) {
//                    if (record.getPayType().equals("alipay")) {
//                        channel = "支付宝";
//                    } else if (record.getPayType().equals("wx")) {
//                        channel = "微信";
//                    } else if (record.getPayType().equals("wx_xcx")) {
//                        channel = "小程序";
//                    }
//                }
                sheet.addCell(new Label(col++, row, record.getOrderNo()));
                sheet.addCell(new Label(col++, row, getOrderTypeName(record)));
                sheet.addCell(new Label(col++, row, time));
                sheet.addCell(new Label(col++, row, record.getOrderStatusName()));
                sheet.addCell(new Label(col++, row, record.getShopName()));
                sheet.addCell(new Label(col++, row, record.getShopAddress()));
                sheet.addCell(new Label(col++, row, record.getShopPhone()));
                sheet.addCell(new Label(col++, row, record.getPostmanName()));
                sheet.addCell(new Label(col++, row, record.getPostmanPhone()));

                int scale5 = 2;//设置位数
                int roundingMode5 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd5 = new BigDecimal((double) distance);
                bd5 = bd5.setScale(scale5, roundingMode5);
                sheet.addCell(new Label(col++, row, bd5.toString()));
                sheet.addCell(new Number(col++, row, record.getDeliveryPrice()));
                sheet.addCell(new Label(col++, row, postmanTime));

                sheet.addCell(new Label(col++, row, record.getBuyer()));
                sheet.addCell(new Label(col++, row, record.getTelephone()));
                sheet.addCell(new Label(col++, row, record.getAddress()));

                double goodsMoney = record.getTotalGoodsMoney();
                int scale1 = 2;//设置位数
                int roundingMode1 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd1 = new BigDecimal((double) goodsMoney);
                bd1 = bd1.setScale(scale1, roundingMode1);
                sheet.addCell(new Number(col++, row, bd1.doubleValue()));
                double deliveryPrice = record.getDeliveryPrice();
                int scale2 = 2;//设置位数
                int roundingMode2 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd2 = new BigDecimal((double) deliveryPrice);
                bd2 = bd2.setScale(scale2, roundingMode2);
                sheet.addCell(new Number(col++, row, bd2.doubleValue()));

                double awardMoney = record.getOriginalYunFeiShop();
                int scale3 = 2;//设置位数
                int roundingMode3 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd3 = new BigDecimal((double) awardMoney);
                bd3 = bd3.setScale(scale3, roundingMode3);
//                awardMoney = bd3.doubleValue();
                sheet.addCell(new Number(col++, row, bd3.doubleValue()));

                // 打包费
                double packetPrice = record.getPacketPrice() == null ? 0.0d : record.getPacketPrice();
                BigDecimal bd6 = new BigDecimal((double) packetPrice);
                bd6 = bd6.setScale(scale2, roundingMode2);
                sheet.addCell(new Number(col++, row, bd6.doubleValue()));

                double totalP = totalPrice;
                int scale4 = 2;//设置位数
                int roundingMode4 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd4 = new BigDecimal((double) totalP);
                bd4 = bd4.setScale(scale4, roundingMode4);
                sheet.addCell(new Number(col++, row, bd4.doubleValue()));

                sheet.addCell(new Label(col++, row, channel));
                // 分成金额
                if (payInfoMap != null) {
                    int scale = 2;//设置位数
                    int roundingMode = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                    // 平台优惠
                    double plateAward = record.getPlatformAwardMoney();
                    BigDecimal plateAwardBD = new BigDecimal((double) plateAward);
                    plateAwardBD = plateAwardBD.setScale(scale, roundingMode);
                    sheet.addCell(new Number(col++, row, plateAwardBD.doubleValue()));
                    // 店铺优惠
                    double shopAward = record.getShopAwardMoney();
                    BigDecimal shopAwardBD = new BigDecimal((double) shopAward);
                    shopAwardBD = shopAwardBD.setScale(scale, roundingMode);
                    sheet.addCell(new Number(col++, row, shopAwardBD.doubleValue()));

                    OrderPayInfo payInfo = payInfoMap.get(record.getOrderNo());
                    if (payInfo == null) {
//                        sheet.addCell(new Label(col++, row, "平台收入"));
//                        sheet.addCell(new Label(col++, row, "店铺收入"));
//                        sheet.addCell(new Label(col++, row, "骑手收入"));
                        sheet.addCell(new Number(col++, row, 0.0d));
                        sheet.addCell(new Number(col++, row, 0.0d));
                        sheet.addCell(new Number(col++, row, 0.0d));
                    } else {
                        // sheet.addCell(new Label(col++, row, "平台收入"));
                        double plateMoney = payInfo.getPlatformMoney();
//                        BigDecimal plateMoney_BD = new BigDecimal((double) plateMoney);
//                        plateMoney_BD = plateMoney_BD.setScale(scale, roundingMode);
                        sheet.addCell(new Number(col++, row, plateMoney));

                        //sheet.addCell(new Label(col++, row, "店铺收入"));
                        double shopMoney = payInfo.getShopMoney();
//                        BigDecimal shopMoney_BD = new BigDecimal((double) shopMoney);
//                        shopMoney_BD = shopMoney_BD.setScale(scale, roundingMode);
                        sheet.addCell(new Number(col++, row, shopMoney));
                        // sheet.addCell(new Label(col++, row, "骑手收入"));
                        double postmanMoney = payInfo.getPostmanMoney();
                        sheet.addCell(new Number(col++, row, postmanMoney));
                    }
                }
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

    // 获取订单类型名称
    private static String getOrderTypeName(OrderInfoVO orderInfo) {
        String nameStr = "";
        if (orderInfo == null) {
            return nameStr;
        }
        if (orderInfo.getOrderType() == null) {
            return nameStr;
        }
        if (orderInfo.getSquareShopId() != null && ValidateUtil.isID(orderInfo.getSquareShopId())) {
            nameStr += "创业商圈订单-";
        }
        switch (orderInfo.getOrderType()) {
            case ORDER_TYPE.SHOP_GOODS:
                nameStr += "外卖";
                break;
            case ORDER_TYPE.PAO_TUI:
                nameStr += "跑腿";
                break;
            case ORDER_TYPE.DAI_GOU:
                nameStr += "代购";
                break;
            case ORDER_TYPE.HUO_YUN:
                nameStr += "货运";
                break;
            case ORDER_TYPE.BANG_BAN:
                nameStr += "帮办";
                break;
            case ORDER_TYPE.GROUP_ORDER:
                nameStr += "团购";
                break;
            default:
                break;
        }
        nameStr += "订单";
        return nameStr;
    }


    //查询所有的提现申请记录导出
    @RequestMapping(method = RequestMethod.POST, value = "/admin/wallet/pop/history/export")
    @ResponseBody
    public String wallet_pop_history_admin_two(String token, String username, Integer popType, Integer isDone, Integer page, Integer size, Long startTime, Long endTime) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //验证是否有登录权限
        if (!User.getInstance().hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (null == page) {
            page = 1;
        }
        if (null == size) {
            size = 10000000;
        }


        QueryCondition query = QueryCondition.getInstance();
        Expression expression = Expression.getInstance();
        expression.addChild("delFlag", 0);
        if (ValidateUtil.isNotNull(username)) {
            expression.addChild("username", "like", username);
        }
        if (null != isDone) {
            //isDone  0所有 1已提现  2所有未提现
            if (0 == 2 - isDone) {
                expression.addChild("isDone", 0);
            } else if (0 == 1 - isDone) {
                expression.addChild("isDone", 1);
            }

        }
        if (null != popType) {
            // 2余额提现 7押金提现 8扣除押金
            expression.addChild("popType", popType);
        }
        query.addCondition(expression);
        query.setPage(page);
        query.setSize(size);
        String sql = DaoUtils.getSql(PojoTables.USER_WALLET_POP, query);
        String sqlCount = DaoUtils.getSqlCount(PojoTables.USER_WALLET_POP, query);
        List<UserWalletPop> list = null;
        Long totalCount = 0l;
        try {
            list = (List<UserWalletPop>) UserDaoSql.getInstance().list(UserWalletPop.class, sql);
            totalCount = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (ValidateUtil.isAbsList(list)) {
            List<UserWalletPopVO> voList = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                UserWalletPop userWalletPop = (UserWalletPop) list.get(i);
                if (null == userWalletPop) {
                    continue;
                }

                if (0 == UserType.SHOP_OWNER - userWalletPop.getUserType()) {
                    ShopInfo shopInfo = ShopMemory.getInstance().getShopById(userWalletPop.getShopId());
                    if (null == shopInfo) {
                        continue;
                    }
                    ShopInfoVO shopInfoVO = new ShopInfoVO();
                    try {
                        ObjectUtils.copyPropertiesExclude(shopInfo, shopInfoVO, null);
                    } catch (Exception e) {
                        e.printStackTrace();
                        continue;
                    }
                    userWalletPop.setShopInfo(shopInfoVO);
                }
                UserWalletPopVO vo = new UserWalletPopVO();
                try {
                    ObjectUtils.copyPropertiesExclude(userWalletPop, vo, null);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                if (ValidateUtil.isNotNull(vo.getUsername())) {
                    UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(vo.getUsername());
                    if (null != userInfo) {
                        vo.setName(userInfo.getName());
//                        if (ValidateUtil.isNotNull(userInfo.getSignPhoto())) {
//                            if (ValidateUtil.isNotNetUrl(userInfo.getSignPhoto())) {
//                                vo.setUserImageUrl(SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfo.getSignPhoto());
//                            } else {
//                                vo.setUserImageUrl(userInfo.getSignPhoto());
//                            }
//                        }
                        vo.setName(userInfo.getName());
                        vo.setTelPhone(userInfo.getTelPhone());
                    }
                }
                voList.add(vo);
            }
            String downloadUrl = null;
            if (ValidateUtil.isAbsList(voList)) {
                downloadUrl = generateWalletPopExcel2(voList);
            }
            if (ValidateUtil.isNotNull(downloadUrl)) {
                return AjaxResult.successResult(SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl);
            }

        }
        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成Excel
    private String generateWalletPopExcel2(List<UserWalletPopVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {

//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.WALLET_POP + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "名称"));
            sheet.addCell(new Label(col++, row, "电话"));
            sheet.addCell(new Label(col++, row, "本次提现金额"));
            sheet.addCell(new Label(col++, row, "提现渠道"));      //  ?
            sheet.addCell(new Label(col++, row, "微信姓名"));
            sheet.addCell(new Label(col++, row, "提现微信账号"));
            sheet.addCell(new Label(col++, row, "支付宝姓名"));
            sheet.addCell(new Label(col++, row, "提现支付宝账号"));
            sheet.addCell(new Label(col++, row, "申请时间"));
            sheet.addCell(new Label(col++, row, "是否提现成功"));
            sheet.addCell(new Label(col++, row, "确定提现时间"));
            row = 1;
            for (UserWalletPopVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(record.getCreateTime()); // 申请时间
                String poptime = "";
                if (record.getIsDone() != 0) {
                    poptime = sdf.format(record.getPopTime()); // 提现时间
                } else {
                    poptime = "";
                }

                String channel = "";
                String isDone = "";
//                if (record.getChannel().equals("alipay")) {
//                    channel = "支付宝";
//                } else {
//                    channel = "微信";
//                }
                if (ValidateUtil.isNotNull(record.getChannel())) {
                    switch (record.getChannel()) {
                        case PAY_TYPE.ALLIPAY: {
                            channel = "支付宝";
                        }
                        break;
                        case PAY_TYPE.WX: {
                            channel = "微信";
                        }
                        break;
                        case PAY_TYPE.BANK_CARD: {
                            channel = "银行卡";
                        }
                        break;
                    }
                }

                if (record.getIsDone() == 0) {
                    isDone = "未成功";
                } else {
                    isDone = "成功";
                }

                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getTelPhone()));
                sheet.addCell(new Number(col++, row, record.getMoney()));
                sheet.addCell(new Label(col++, row, channel));      //  ?
//            if (record.getType() == 1) {
//                sheet.addCell(new Number(col++, row, record.getMoney(), wcfN));
//                sheet.addCell(new Label(col++, row, ""));
//            } else {
//                sheet.addCell(new Label(col++, row, ""));
//                sheet.addCell(new Number(col++, row, record.getMoney(), wcfN));
//            }
                sheet.addCell(new Label(col++, row, record.getTenpayRealName(), wcfN));    //   shisha
                sheet.addCell(new Label(col++, row, record.getTenpay()));
                sheet.addCell(new Label(col++, row, record.getAlipayRealName()));
                sheet.addCell(new Label(col++, row, record.getAlipay()));
                sheet.addCell(new Label(col++, row, time));
                sheet.addCell(new Label(col++, row, isDone));     //  ?
                sheet.addCell(new Label(col++, row, poptime));

                row++;     // 行
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    // 查询扣除押金记录导出（laoke） BY LMY（检索城市）
    @RequestMapping(method = RequestMethod.POST, value = "/admin/wallet/pop/history/V2/export")
    @ResponseBody
    public String user_wallet_history_admin_export_V2(String token, String username, Integer userType, Integer popType,
                                                      Long startTime, Long endTime, Integer isDone, String province, String county, String city,
                                                      Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //验证是否有登录权限
        if (!User.getInstance().hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (!ValidateUtil.isAbsInteger(userType)) {
            return AjaxResult.errorResult("用户类型不能为空");
        }

        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }
        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 20;
        }
        Long totalCount = 0l;

        String timeStr = "";
        if (null != startTime) {
            timeStr = " and a.createTime >= " + startTime;
        }
        if (null != endTime) {
            timeStr = " and a.createTime <= " + endTime;
        }

        StringBuffer sb = new StringBuffer();
        if (0 == userType - UserType.POSTMAN) {
            sb.append(" FROM ").append(PojoTables.USER_WALLET_POP).append(" a ")
                    .append(" left join ").append(PojoTables.CityPostman).append(" b on a.username = b.username")
                    .append(" where b.delFlag = 0 and a.delFlag =0 ").append(timeStr);
        } else {
            sb.append(" FROM ").append(PojoTables.USER_WALLET_POP).append(" a ")
                    .append(" left join ").append(PojoTables.ShopInfo).append(" b on a.username = b.ownUsername ")
                    .append(" where b.id is not null and b.delFlag = 0 and a.delFlag =0 ").append(timeStr);
        }

        //isDone  0所有 1已提现  2所有未提现
        if (ValidateUtil.isAbsInteger(isDone) && isDone > 0) {
            if (0 == 2 - isDone) {
                sb.append(" and a.isDone=0 ");
            } else {
                sb.append(" and a.isDone=1 ");
            }
        }
        if (null != popType) {
            // 2余额提现 7押金提现 8扣除押金
            sb.append(" and a.popType= ").append(popType);
        }
        if (ValidateUtil.isNotNull(username)) {
            sb.append(" and a.username ='").append(username).append("'");
        }
        if (ValidateUtil.isNotNull(province)) {
            sb.append(" and b.province ='").append(province).append("'");
        }
        if (ValidateUtil.isNotNull(city)) {
            sb.append(" and b.city ='").append(city).append("'");
        }
        if (ValidateUtil.isNotNull(county)) {
            sb.append(" and b.county ='").append(county).append("'");
        }


        try {
            StringBuffer sbCount = new StringBuffer();
            sbCount.append("select count(1) counts ");
            sbCount.append(sb.toString());
            totalCount = UserDaoSql.getInstance().getCount(sbCount.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("查询失败，请稍后再试");
        }

        List<?> list = null;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT a.*, b.province ,b.city ,b.county ");
            sbSql.append(sb.toString());
            sbSql.append(" LIMIT ").append((page - 1) * size).append(",").append(size);
            list = UserDaoSql.getInstance().list(UserWalletPopVO.class, sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("查询失败，请稍后再试");
        }

        if (ValidateUtil.isAbsList(list)) {

            List<UserWalletPopVO> voList = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                UserWalletPopVO vo = (UserWalletPopVO) list.get(i);
                if (null == vo) {
                    continue;
                }

//                UserWalletPopVO vo = new UserWalletPopVO();
//                try {
//                    ObjectUtils.copyPropertiesExclude(userWalletPop, vo, null);
//                } catch (Exception e) {
//                    e.printStackTrace();
//                    continue;
//                }


                if (ValidateUtil.isNotNull(vo.getUsername())) {
                    UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(vo.getUsername());
                    if (null != userInfo) {
                        vo.setName(userInfo.getName());
                        if (ValidateUtil.isNotNull(userInfo.getSignPhoto())) {
                            if (ValidateUtil.isNotNetUrl(userInfo.getSignPhoto())) {
                                vo.setUserImageUrl(SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfo.getSignPhoto());
                            } else {
                                vo.setUserImageUrl(userInfo.getSignPhoto());
                            }
                        }
                        vo.setName(userInfo.getName());
                        vo.setTelPhone(userInfo.getTelPhone());
                    }
                }
                voList.add(vo);
            }
//            if (ValidateUtil.isAbsList(voList)) {
//                return AjaxResult.successResult(totalCount, voList.size(), voList);
//            }
            String downloadUrl = null;
            if (ValidateUtil.isAbsList(voList)) {
                downloadUrl = generateWalletPopExcel2(voList);
            }
            if (ValidateUtil.isNotNull(downloadUrl)) {
                return AjaxResult.successResult(SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl);
            }
        }

        return AjaxResult.successResult(0, 0, null);
    }


    //查询扣除押金记录导出（laoke）
    @RequestMapping(method = RequestMethod.POST, value = "/admin/deposit/history/export")
    @ResponseBody
    public String wallet_pop_history_admin_three(String token, String username, Integer popType, Integer isDone, Integer page, Integer size, Long startTime, Long endTime) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //验证是否有登录权限
        if (!User.getInstance().hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (null == page) {
            page = 1;
        }
        if (null == size) {
            size = 10000000;
        }


        QueryCondition query = QueryCondition.getInstance();
        Expression expression = Expression.getInstance();
        expression.addChild("delFlag", 0);
        if (ValidateUtil.isNotNull(username)) {
            expression.addChild("username", "like", username);
        }
        if (null != isDone) {
            //isDone  0所有 1已提现  2所有未提现
            if (0 == 2 - isDone) {
                expression.addChild("isDone", 0);
            } else if (0 == 1 - isDone) {
                expression.addChild("isDone", 1);
            }

        }
        if (null != popType) {
            // 2余额提现 7押金提现 8扣除押金
            expression.addChild("popType", popType);
        }
        query.addCondition(expression);
        query.setPage(page);
        query.setSize(size);
        String sql = DaoUtils.getSql(PojoTables.USER_WALLET_POP, query);
        String sqlCount = DaoUtils.getSqlCount(PojoTables.USER_WALLET_POP, query);
        List<UserWalletPop> list = null;
        Long totalCount = 0l;
        try {
            list = (List<UserWalletPop>) UserDaoSql.getInstance().list(UserWalletPop.class, sql);
            totalCount = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (ValidateUtil.isAbsList(list)) {
            List<UserWalletPopVO> voList = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                UserWalletPop userWalletPop = (UserWalletPop) list.get(i);
                if (null == userWalletPop) {
                    continue;
                }

                if (0 == UserType.SHOP_OWNER - userWalletPop.getUserType()) {
                    ShopInfo shopInfo = ShopMemory.getInstance().getShopById(userWalletPop.getShopId());
                    if (null == shopInfo) {
                        continue;
                    }
                    ShopInfoVO shopInfoVO = new ShopInfoVO();
                    try {
                        ObjectUtils.copyPropertiesExclude(shopInfo, shopInfoVO, null);
                    } catch (Exception e) {
                        e.printStackTrace();
                        continue;
                    }
                    userWalletPop.setShopInfo(shopInfoVO);
                }
                UserWalletPopVO vo = new UserWalletPopVO();
                try {
                    ObjectUtils.copyPropertiesExclude(userWalletPop, vo, null);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                if (ValidateUtil.isNotNull(vo.getUsername())) {
                    UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(vo.getUsername());
                    if (null != userInfo) {
                        vo.setName(userInfo.getName());
//                        if (ValidateUtil.isNotNull(userInfo.getSignPhoto())) {
//                            if (ValidateUtil.isNotNetUrl(userInfo.getSignPhoto())) {
//                                vo.setUserImageUrl(SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfo.getSignPhoto());
//                            } else {
//                                vo.setUserImageUrl(userInfo.getSignPhoto());
//                            }
//                        }
                        vo.setName(userInfo.getName());
                        vo.setTelPhone(userInfo.getTelPhone());
                    }
                }
                voList.add(vo);
            }
            String downloadUrl = null;
            if (ValidateUtil.isAbsList(voList)) {
                downloadUrl = generateWalletPopExcel3(voList);
            }
            if (ValidateUtil.isNotNull(downloadUrl)) {
                return AjaxResult.successResult(SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl);
            }

        }
        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成Excel
    private String generateWalletPopExcel3(List<UserWalletPopVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {

//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.WALLET_POP + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "名称"));
            sheet.addCell(new Label(col++, row, "账号"));
            sheet.addCell(new Label(col++, row, "扣除金额"));
            sheet.addCell(new Label(col++, row, "扣除原因"));      //  ?
            sheet.addCell(new Label(col++, row, "扣除时间"));
            sheet.addCell(new Label(col++, row, "操作人"));
            row = 1;
            for (UserWalletPopVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(record.getCreateTime()); // 扣除时间

                String channel = "";

                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getUsername()));
                sheet.addCell(new Number(col++, row, record.getMoney()));
                sheet.addCell(new Label(col++, row, record.getNote()));
                sheet.addCell(new Label(col++, row, time));
                sheet.addCell(new Label(col++, row, record.getCreateUser()));
                row++;     // 行
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    //管理后台导出Excel订单列表(老克工单)
    @RequestMapping(method = RequestMethod.POST, value = "/admin/Order/laoke/export")
    @ResponseBody
    public String order_laoke_list(String token,
                                   String city,
                                   String shopUsername, String shopName,//店铺信息
                                   String name, String phone, String address, //购买人信息
                                   String orderNo, //订单编号
                                   Integer orderStatus,//订单状态
                                   Long startTime, Long endTime,
                                   Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        //验证用户是否有权限
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }


        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }

        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 1000000;
        }
        List<OrderInfo> list = new ArrayList<>();

        //不按店铺查
        StringBuffer sb = new StringBuffer();
        //数据库中查出订单编号  再去缓存取内容
        if (ValidateUtil.isNotNull(shopUsername) || ValidateUtil.isNotNull(shopName)) {

            sb.append(" from ").append(PojoTables.OrderInfo).append(" a left join ").append(PojoTables.ShopInfo)
                    .append(" b on a.shopId = b.id where b.id is not null and b.delFlag = 0 and a.delFlag = 0 ");
//                    .append(" and ( 1=1 ");

            if (ValidateUtil.isNotNull(shopUsername)) {
                sb.append(" and b.shopName like '%").append(shopUsername).append("%' ");
            }
            if (ValidateUtil.isNotNull(shopName)) {
                sb.append(" and b.shopName like '%").append(shopName).append("%' ");
            }

            if (ValidateUtil.isNotNull(name)) {
                sb.append(" and a.buyer like '%").append(name).append("%' ");
            }
            if (ValidateUtil.isNotNull(phone)) {
                sb.append(" and a.telephone like '%").append(phone).append("%' ");
            }
            if (ValidateUtil.isNotNull(address)) {
                sb.append(" and ( a.address like '%").append(address).append("%' ");
                sb.append(" or a.address2 like '%").append(address).append("%' )");
            }
            if (ValidateUtil.isNotNull(orderNo)) {
                sb.append(" and a.orderNo like '%").append(orderNo).append("%' ");
            }


            if (null != orderStatus && orderStatus > 0) {
                sb.append(" and a.orderStatus = ").append(orderStatus);
            }

            if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
                sb.append(" and a.createTime Between ").append(startTime).append(" and ").append(endTime);
            }

            if (ValidateUtil.isNull(city)) {
                sb.append(" and a.shopCity = '").append(city).append("'");
            }

        } else {
            sb.append(" from ").append(PojoTables.OrderInfo).append(" a where delFlag = 0 ");
//                    .append(" and ( 1=1 ");

            if (ValidateUtil.isNotNull(name)) {
                sb.append(" and buyer like '%").append(name).append("%' ");
            }
            if (ValidateUtil.isNotNull(phone)) {
                sb.append(" and telephone like '%").append(phone).append("%' ");
            }
            if (ValidateUtil.isNotNull(address)) {
                sb.append(" and ( address like '%").append(address).append("%' ");
                sb.append(" or address2 like '%").append(address).append("%' ) ");
            }
            if (ValidateUtil.isNotNull(orderNo)) {
                sb.append(" and orderNo like '%").append(orderNo).append("%' ");
            }
//            sb.append(") ");
            if (null != orderStatus && orderStatus > 0) {
                sb.append(" and orderStatus = ").append(orderStatus);
            }

            if (ValidateUtil.isAbsLong(startTime) && ValidateUtil.isAbsLong(endTime)) {
                sb.append(" and createTime Between ").append(startTime).append(" and ").append(endTime);
            }
            if (ValidateUtil.isNotNull(city)) {
                sb.append(" and shopCity = '").append(city).append("'");
            }

        }
        Long totalCount = 0l;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select count(a.orderNo) as counts ");
            sbSql.append(sb.toString());
            totalCount = UserDaoSql.getInstance().getCount(sbSql.toString());
//            System.out.println(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            totalCount = 0l;
        }


        List<HashMap<String, Object>> listMap = null;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("select  a.orderNo ");
            sbSql.append(sb.toString());
            sbSql.append(" limit ").append((page - 1) * size).append(",").append(size);
            listMap = UserDaoSql.getInstance().queryBySql(sbSql.toString());
//            System.out.println(sbSql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            listMap = null;
        }
        List<OrderInfoVO> voList = new ArrayList<OrderInfoVO>();
        if (ValidateUtil.isAbsList(listMap)) {

            for (int i = 0; i < listMap.size(); i++) {
                if (null == listMap.get(i)) {
                    continue;
                }
                String no = (String) listMap.get(i).get("orderNo");
                if (ValidateUtil.isNull(no)) {
                    continue;
                }
                OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(no);
                if (null == orderInfo) {
                    continue;
                }

                OrderInfoVO orderInfoVO = new OrderInfoVO();
                try {
                    ObjectUtils.copyPropertiesExclude(orderInfo, orderInfoVO, null);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }


                String postmanUsername = orderInfo.getPostManUsername();

                if (ValidateUtil.isNotNull(postmanUsername)) {
                    CityPostman cityPostman = CityPostmanMemory.getInstance().getPostman(postmanUsername);
                    if (cityPostman != null) {
                        CityPostmanVO cityPostmanVO = new CityPostmanVO();
                        try {
                            ObjectUtils.copyPropertiesExclude(cityPostman, cityPostmanVO, null);
                        } catch (Exception e) {
                            e.printStackTrace();
                            continue;
                        }
                        orderInfoVO.setPostmanInfo(cityPostmanVO);
                    }

                }

                voList.add(orderInfoVO);
            }
        }

        if (ValidateUtil.isAbsList(voList)) {
            String downloadUrl = laokeOrderExcel(voList);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
        }

        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成Excel
    private String laokeOrderExcel(List<OrderInfoVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);
//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "工单编号"));
            sheet.addCell(new Label(col++, row, "工单状态"));
            sheet.addCell(new Label(col++, row, "工单有效性"));
            sheet.addCell(new Label(col++, row, "发单标志"));
            sheet.addCell(new Label(col++, row, "加急标志"));
            sheet.addCell(new Label(col++, row, "是否预约"));
//            sheet.addCell(new Label(col++, row, "超时标志"));

            sheet.addCell(new Label(col++, row, "发单时间"));
//            sheet.addCell(new Label(col++, row, "编制时间"));
//            sheet.addCell(new Label(col++, row, "下达时间"));
            sheet.addCell(new Label(col++, row, "抢单时间"));
            sheet.addCell(new Label(col++, row, "完成时间"));
            sheet.addCell(new Label(col++, row, "预约时间"));
//            sheet.addCell(new Label(col++, row, "作废时间"));
            sheet.addCell(new Label(col++, row, "回访时间"));

//            sheet.addCell(new Label(col++, row, "抢单用时"));  [时间长度]
//            sheet.addCell(new Label(col++, row, "完成用时"));
//            sheet.addCell(new Label(col++, row, "处结用时"));
            sheet.addCell(new Label(col++, row, "发单人"));
//            sheet.addCell(new Label(col++, row, "发单人等级"));
            sheet.addCell(new Label(col++, row, "编制人"));
            sheet.addCell(new Label(col++, row, "抢单人"));
            sheet.addCell(new Label(col++, row, "抢单人等级"));
//            sheet.addCell(new Label(col++, row, "作废人"));
//            sheet.addCell(new Label(col++, row, "回访人"));


            sheet.addCell(new Label(col++, row, "附件数"));
            sheet.addCell(new Label(col++, row, "联系人"));
            sheet.addCell(new Label(col++, row, "联系电话"));
            sheet.addCell(new Label(col++, row, "用户地址"));
            sheet.addCell(new Label(col++, row, "楼号-门牌号"));
            sheet.addCell(new Label(col++, row, "身份证号"));
            sheet.addCell(new Label(col++, row, "所在城市"));
            sheet.addCell(new Label(col++, row, "所在市区"));
//            sheet.addCell(new Label(col++, row, "一级工单标签"));
//            sheet.addCell(new Label(col++, row, "二级工单标签"));
            sheet.addCell(new Label(col++, row, "工单标签"));

            sheet.addCell(new Label(col++, row, "评价结果"));
            sheet.addCell(new Label(col++, row, "回访时间"));
            sheet.addCell(new Label(col++, row, "工单备注"));

            sheet.addCell(new Label(col++, row, "开锁执行人分成比例"));
            sheet.addCell(new Label(col++, row, "开锁平台分成比例"));
            sheet.addCell(new Label(col++, row, "开锁执行人分成金额"));
            sheet.addCell(new Label(col++, row, "开锁费用"));

            sheet.addCell(new Label(col++, row, "换锁执行人分成比例"));
            sheet.addCell(new Label(col++, row, "换锁执行人分成金额"));
            sheet.addCell(new Label(col++, row, "换锁费用"));
            sheet.addCell(new Label(col++, row, "换锁平台分成比例"));
            sheet.addCell(new Label(col++, row, "平台手续费"));


//            sheet.addCell(new Label(col++, row, "锁匠姓名"));
//            sheet.addCell(new Label(col++, row, "锁匠电话"));

            row = 1;
            for (OrderInfoVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                String time = sdf.format(record.getCreateTime()); // 申请时间
//                String time2 = sdf.format(record.getServiceSureTime()); // 编制时间
//                String time3 = "";  // 下达时间
//                if (record.getServicePushTime() != 0) {
//                    time3 = sdf.format(record.getServicePushTime());
//                } else {
//                    time3 = "";
//                }
                String time4 = "";  // 抢单时间
                if (record.getPostmanSureTime() != 0) {
                    time4 = sdf.format(record.getPostmanSureTime());
                } else {
                    time4 = "";
                }
                String time5 = "";  // 完成时间
                if (record.getPostmanFinishTime() != 0) {
                    time5 = sdf.format(record.getPostmanFinishTime());
                } else {
                    time5 = "";
                }
                String time6 = "";  // 预约时间
                if (record.getZiQuTime() != 0) {
                    time6 = sdf.format(record.getZiQuTime());
                } else {
                    time6 = "";
                }
//                String time7 = sdf.format(record.get); // 作废时间

                String time8 = "";  // 回访时间
                if (record.getUserFinishTime() != 0) {
                    time8 = sdf.format(record.getUserFinishTime());
                } else {
                    time8 = "";
                }

                String orderType = "";
                String submitType = "";
                String isUrgent = "";    // 是否加急
                String isAppointment = "";    // 是否预约
                String isValidity = "";  // 工单有效性
//                if (ValidateUtil.isNull(record.getShopId())) {
//                    switch (record.getOrderStatus()) {
//                        case -3: {
//                            submitType = "平台发单";
//                        }
//                        break;
//                        case -4: {
//                            submitType = "锁匠发单";
//                        }
//                        break;
//                    }
//                }
                if (record.getShopId() == -3) {
                    submitType = "平台发单";
                } else if (record.getShopId() == -4) {
                    submitType = "锁匠发单";
                }
                if (record.getIsUrgent() == 1) {
                    isUrgent = "加急";
                } else {
                    isUrgent = "正常";
                }
                if (record.getIsAppointment() == 1) {
                    isAppointment = sdf.format(record.getZiQuTime());
                    ;
                } else {
                    isAppointment = "正常";
                }

                if (record.getOrderStatus() == 0 || record.getOrderStatus() == 3 || record.getOrderStatus() == 4 || record.getOrderStatus() == 33 || record.getOrderStatus() == 41) {
                    isValidity = "无效";
                } else {
                    isValidity = "有效";
                }

                if (ValidateUtil.isAbsInteger(record.getOrderStatus())) {
                    switch (record.getOrderStatus()) {
                        case 1: {
                            orderType = "未认领";
                        }
                        break;
                        case 19: {
                            orderType = "未处结";
                        }
                        break;
                        case 2: {
                            orderType = "未回访";
                        }
                        break;
                        case 9: {
                            orderType = "订单完成";
                        }
                        break;
                        case 33: {
                            orderType = "已退回";
                        }
                        break;
                        case 3: {
                            orderType = "已作废";
                        }
                        break;
                    }
                }
//                发单人信息
//                String level = 0 + "";
//                Integer level2 = 0;  // 抢单人等级
                String level2 = 1 + "";
//                if (record.getShopId() == -4) {   //  -3 平台发单  -4锁匠发单
//                    if (null != record.getPostmanInfo()) {
//                        level = record.getPostmanInfo().getLevel() + "";
//                    }
//                }
                OrderPayInfo orderPayInfo = OrderPayMemory.getInstance().get(record.getOrderNo());
                if (null != orderPayInfo) {
                    String orderPayStr = orderPayInfo.getExtra3();
                    JSONObject json = JSONObject.fromObject(orderPayStr);
                    System.out.println(json);
                    if (json.has("level")) // 在此处做判断，包含字段的话再继续从JSON中获取level字段的内容
                    {
                        level2 = json.getString("level");
                    }
                }
                sheet.addCell(new Label(col++, row, record.getOrderNo()));  // 工单标志
                sheet.addCell(new Label(col++, row, orderType)); // 工单状态
                sheet.addCell(new Label(col++, row, isValidity));   // 工单有效性
                sheet.addCell(new Label(col++, row, submitType)); // 发单标志
                sheet.addCell(new Label(col++, row, isUrgent)); // 加急标志
                sheet.addCell(new Label(col++, row, isAppointment)); // 预约时间
//                sheet.addCell(new Label(col++, row, isUrgent)); // 超时标志

                sheet.addCell(new Label(col++, row, time));   // 发单时间
//                sheet.addCell(new Label(col++, row, time2)); // 编制时间
//                sheet.addCell(new Label(col++, row, time3));  // 下达时间
                sheet.addCell(new Label(col++, row, time4));  // 抢单时间
                sheet.addCell(new Label(col++, row, time5));  // 完成时间
                sheet.addCell(new Label(col++, row, time6)); // 预约时间
//                sheet.addCell(new Label(col++, row, record.getBuyer())); // 作废时间
                sheet.addCell(new Label(col++, row, time8)); // 回访时间

//                sheet.addCell(new Label(col++, row, record.getBuyer()));  // 抢单用时
//                sheet.addCell(new Label(col++, row, record.getTelephone())); // 完成用时
//                sheet.addCell(new Label(col++, row, record.getExtra1()));   // 未处结用时

                String submitName = "";   // 发单人 （）
                String makeName = "";     // 编制人  （客服）
                String robName = "";   // 抢单人
//                String cancelName = "";   // 作废人 /回访人

                if (record.getShopId() == -4) {   //   -4锁匠发单  -3 平台发单
                    if (ValidateUtil.isNotNull(record.getUsername())) {
                        UserInfo user = UserInfoMemory.getInstance().getUserInfo(record.getUsername());
                        if (user != null) {
                            submitName = user.getName();
                        }
                    }
                }
                if (record.getShopId() == -3) {
                    if (ValidateUtil.isNotNull(record.getCreateUser())) { // 编制人  （客服）
                        UserInfo user = UserInfoMemory.getInstance().getUserInfo(record.getCreateUser());
                        if (user != null) {
                            makeName = user.getName();
                        }
                    }
                }

                if (ValidateUtil.isNotNull(record.getPostManUsername())) {
                    UserInfo user = UserInfoMemory.getInstance().getUserInfo(record.getPostManUsername());
                    if (user != null) {
                        robName = user.getName();
                    }
                }
//                if (ValidateUtil.isNotNull(record.getServiceUsername())) {
//                    UserInfo user = UserInfoMemory.getInstance().getUserInfo(record.getServiceUsername());
//                    if (user != null) {
//                        cancelName = user.getName();
//                    }
//                }
                sheet.addCell(new Label(col++, row, submitName));  //发单人
//                sheet.addCell(new Label(col++, row, level + "")); //发单人等级
                sheet.addCell(new Label(col++, row, makeName));//编制人
                sheet.addCell(new Label(col++, row, robName));//抢单人
                sheet.addCell(new Label(col++, row, level2));//抢单人等级
//                sheet.addCell(new Label(col++, row, cancelName));//作废人
//                sheet.addCell(new Label(col++, row, makeName));//回访人

                if (ValidateUtil.isNotNull(record.getOrderNo())) {
                    Integer imageCount = OrderInfoImageMemory.getInstance().getCountOfOrderNo(record.getOrderNo());
                    if (imageCount != null) {
                        sheet.addCell(new Label(col++, row, imageCount + ""));    // 附件数
                    }
                }
                sheet.addCell(new Label(col++, row, record.getBuyer()));
                sheet.addCell(new Label(col++, row, record.getTelephone()));
                sheet.addCell(new Label(col++, row, record.getAddress())); // 用户地址
                sheet.addCell(new Label(col++, row, record.getAddress2())); // 楼号-门牌号
                sheet.addCell(new Label(col++, row, record.getExtra1()));       //  身份证号
                sheet.addCell(new Label(col++, row, record.getCity())); // 所在城市
                sheet.addCell(new Label(col++, row, record.getCounty())); // 所在市区
                String lableName = "";
                if (ValidateUtil.isNotNull(record.getOrderNo())) {
                    List<OrderGoodsLabel> labelList = OrderGoodsLabelMemory.getInstance().listOfOrderNo(record.getOrderNo());
                    if (null != labelList) {
                        for (int i = 0; i < labelList.size(); i++) {
                            lableName += labelList.get(i).getName() + ",";
                        }
                    }
                }
                sheet.addCell(new Label(col++, row, lableName)); // 标签
                sheet.addCell(new Label(col++, row, record.getExtra2())); // 评价结果
                sheet.addCell(new Label(col++, row, time8)); // 回访时间
                String note = record.getNote();
                if (record.getNote().equals("null") || record.getNote() == null) {
                    note = "";
                }
                sheet.addCell(new Label(col++, row, note)); // 工单备注

                if (ValidateUtil.isNotNull(record.getOrderNo())) {
                    if (null != orderPayInfo) {
                        String orderPayStr = orderPayInfo.getExtra3();
                        JSONObject json = JSONObject.fromObject(orderPayStr);
                        String openExeRate = json.getString("openExeRate");
                        String openPlatRate = json.getString("openPlatRate");
                        String openExeMoney = json.getString("openExeMoney");
                        String openMoney = json.getString("openMoney");

                        String changeExeRate = json.getString("changeExeRate");
                        String changeExeMoney = json.getString("changeExeMoney");
                        String changeMoney = json.getString("changeMoney");
                        String changePlatRate = json.getString("changePlatRate");
                        String platRate = json.getString("platRate");

                        sheet.addCell(new Label(col++, row, openExeRate));    // 开锁执行人分成比例
                        sheet.addCell(new Label(col++, row, openPlatRate));
                        sheet.addCell(new Label(col++, row, openExeMoney));
                        sheet.addCell(new Label(col++, row, openMoney));

                        sheet.addCell(new Label(col++, row, changeExeRate));    // 开锁执行人分成比例
                        sheet.addCell(new Label(col++, row, changeExeMoney));
                        sheet.addCell(new Label(col++, row, changeMoney));
                        sheet.addCell(new Label(col++, row, changePlatRate));
                        sheet.addCell(new Label(col++, row, platRate));
                    }
                }

                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    // 锁匠列表（待审核/退回） 管理员
    @RequestMapping(method = RequestMethod.POST, value = "/admin/postman/verify/back/list/export")
    @ResponseBody
    public String verify_back_list(String token, String city, String username, Long startTime, Long endTime,
                                   String province, String county, Integer page, Integer size, Integer type) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }
        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 10000000;
        }

        QueryCondition query = QueryCondition.getInstance();
        Expression expression = Expression.getInstance();
        expression.addChild("delFlag", 0);//所有未删除的
        if (ValidateUtil.isNotNull(city)) {
            expression.addChild("city", city);
        }
        if (ValidateUtil.isNotNull(province)) {
            expression.addChild("province", province);
        }
        if (ValidateUtil.isNotNull(county)) {
            expression.addChild("county", county);
        }
        if (ValidateUtil.isNotNull(username)) {
            expression.addChild("username", "like", username);
        }
        if (null != startTime) {
            expression.addChild("createTime", ">=", startTime);
        }
        if (null != endTime) {
            expression.addChild("createTime", "<=", endTime);
        }
        if (type == 1) {
            expression.addChild("isVerify", 0);//未审核
            expression.addChild("verifyStatus", POSTMAN_VERIFY_STATUS.ING);
        } else {
            expression.addChild("isVerify", 2);//未审核
            expression.addChild("verifyStatus", POSTMAN_VERIFY_STATUS.BACK);
        }

        query.addCondition(expression);
        query.addOrders("createTime", "desc");
        query.setPage(page);
        query.setSize(size);

        String sql = DaoUtils.getSql(PojoTables.POSTMAN_VERITY, query);
        String sqlCount = DaoUtils.getSqlCount(PojoTables.POSTMAN_VERITY, query);
        List<CityPostmanVerifyVO> list = null;
        Long totalCount = 0l;
        try {
            list = (List<CityPostmanVerifyVO>) UserDaoSql.getInstance().list(CityPostmanVerifyVO.class, sql);
            totalCount = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("获取失败，请稍后再试");
        }

        String downloadUrl = laokeUserExcel(list);
        if (ValidateUtil.isNotNull(downloadUrl)) {
            String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
            return AjaxResult.successResult(url);
        }

        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成Excel
    private String laokeUserExcel(List<CityPostmanVerifyVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行

            sheet.addCell(new Label(col++, row, "认证人"));
            sheet.addCell(new Label(col++, row, "手机号"));
            sheet.addCell(new Label(col++, row, "身份证号"));

            sheet.addCell(new Label(col++, row, "提交审核日期"));
            sheet.addCell(new Label(col++, row, "服务地区"));
            sheet.addCell(new Label(col++, row, "执照有效期"));
            sheet.addCell(new Label(col++, row, "执照类型"));
            sheet.addCell(new Label(col++, row, "执照名称"));
            sheet.addCell(new Label(col++, row, "执照注册号"));

//            sheet.addCell(new Label(col++, row, "技能标签"));

            row = 1;
            for (CityPostmanVerifyVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(record.getCreateTime()); // 申请时间

                String licenceDate = "";
                String licenceType = "";

                if (record.getLicenceDate() == 0) {
                    licenceDate = "永久";
                } else {
                    licenceDate = sdf.format(record.getLicenceDate()); // 申请时间
                }
                if (record.getLicenceType() == 1) {
                    licenceType = "从业资格证";
                } else {
                    licenceType = "特许执照";
                }

                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getTelephone()));
                sheet.addCell(new Label(col++, row, record.getOwnerID()));

                sheet.addCell(new Label(col++, row, time));   // 提交审核日期
                sheet.addCell(new Label(col++, row, record.getProvince() + record.getCity() + record.getCounty())); // 服务地区
                sheet.addCell(new Label(col++, row, licenceDate));  // 执照有效期
                sheet.addCell(new Label(col++, row, licenceType));  // 执照类型
                sheet.addCell(new Label(col++, row, record.getLicenceName()));  // 执照名称
                sheet.addCell(new Label(col++, row, record.getLicence())); // 执照注册号

                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    //导出所有用户
    @RequestMapping(method = RequestMethod.POST, value = "/admin/user/list/export")
    @ResponseBody
    public String admin_user_export_list(String token, String username, String content, Long startTime, Long endTime, String city, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        List<UserInfoVO> list = getUserList(UserType.USER, false, startTime, endTime);
        if (ValidateUtil.isAbsList(list)) {
            List<UserInfoVO> tmpList = new ArrayList<UserInfoVO>();
            String totalCount = null;
            if (ValidateUtil.isNotNull(username) || ValidateUtil.isNotNull(content) || ValidateUtil.isNotNull(city)) {
                for (int i = 0; i < list.size(); i++) {
                    if (null == list.get(i)) {
                        continue;
                    }
                    UserInfoVO userInfoVO = list.get(i);

                    //账号
                    if (ValidateUtil.isNotNull(username)) {
                        if (ValidateUtil.isNotNull(userInfoVO.getUsername()) && userInfoVO.getTelPhone().contains(username)) {
                            tmpList.add(list.get(i));
                            continue;
                        }

                        if (ValidateUtil.isNotNull(userInfoVO.getTelPhone()) && userInfoVO.getTelPhone().contains(username)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getPhone2()) && userInfoVO.getPhone2().contains(username)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                    }

                    //内容
                    if (ValidateUtil.isNotNull(content)) {
                        if (userInfoVO.getUsername().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getName()) && userInfoVO.getName().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getCity()) && userInfoVO.getCity().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getTelPhone()) && userInfoVO.getTelPhone().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getPhone2()) && userInfoVO.getPhone2().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getAddress()) && userInfoVO.getAddress().contains(content)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                    }

                    //城市
                    if (ValidateUtil.isNotNull(city)) {
                        if (ValidateUtil.isNotNull(userInfoVO.getCity()) && userInfoVO.getCity().contains(city)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                        if (ValidateUtil.isNotNull(userInfoVO.getAddress()) && userInfoVO.getAddress().contains(city)) {
                            tmpList.add(list.get(i));
                            continue;
                        }
                    }
                }
            } else {
                tmpList.addAll(list);
            }


            //分页返回
//            JYPageUtils.PageList pageList = JYPageUtils.getList(tmpList, page, size);
//            if (null != pageList && ValidateUtil.isAbsList(pageList.list)) {
//                List<UserInfoVO> voList = new ArrayList<UserInfoVO>();
//                for (int i = 0; i < pageList.list.size(); i++) {
//                    if (null == pageList.list.get(i)) {
//                        continue;
//                    }
//                    voList.add((UserInfoVO) pageList.list.get(i));
//                }
//
//
//            }
            if (ValidateUtil.isAbsList(tmpList)) {
//                return AjaxResult.successResult(tmpList.totalCount, tmpList.size(), tmpList);
                String downloadUrl = laokeUser2Excel(tmpList);
                if (ValidateUtil.isNotNull(downloadUrl)) {
                    String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                    return AjaxResult.successResult(url);
                }
            }
        }


        return AjaxResult.successResult(0, 0, null);
    }

    private List<UserInfoVO> getUserList(int userType, boolean isLock, Long startTime, Long endTime) {
        List<UserInfo> list = UserInfoMemory.getInstance().list(userType);
        if (null == list) {
            return null;
        }

        List<UserInfoVO> voList = new ArrayList<UserInfoVO>();
        for (int i = 0; i < list.size(); i++) {
            if (null == list.get(i)) {
                continue;
            }

            if (isLock && !User.isLocked(list.get(i).getIsLock())) {
                continue;
            }

            if (null != startTime) {
                if (list.get(i).getCreateTime() - startTime < 0) {
                    continue;
                }
            }
            if (null != endTime) {
                if (list.get(i).getCreateTime() - endTime > 0) {
                    continue;
                }
            }

            UserInfoVO userInfoVO = new UserInfoVO();
            try {
                ObjectUtils.copyPropertiesExclude(list.get(i), userInfoVO, null);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            if (null == userInfoVO) {
                continue;
            }
            if (ValidateUtil.isNotNull(userInfoVO.getSignPhoto()) && ValidateUtil.isNotNetUrl(userInfoVO.getSignPhoto())) {
                userInfoVO.setSignPhoto(SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfoVO.getSignPhoto());
            }
            voList.add(userInfoVO);
        }

        return voList;
    }

    //---------------------------------生成Excel
    private String laokeUser2Excel(List<UserInfoVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行

            sheet.addCell(new Label(col++, row, "登录账号"));
            sheet.addCell(new Label(col++, row, "注册时间"));

            row = 1;
            for (UserInfoVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = sdf.format(record.getCreateTime()); // 申请时间

                sheet.addCell(new Label(col++, row, record.getUsername()));
                sheet.addCell(new Label(col++, row, time));

                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    //配送员列表导出excel（按城市）
    @RequestMapping(method = RequestMethod.POST, value = "/city/postman/list/export")
    @ResponseBody
    public String city_postman_export_list(String token,
                                           String city, String province, String county, String region,
                                           Integer isLock,//是否已停用  0未锁定  1锁定  2全部
                                           Integer isVerify,//-1或null全部   0未认证 1已认证 2认证中
                                           Long shopId,   // 是否是店铺获取配送员
                                           Integer type,   // 配送员类型
                                           String username,
                                           Long startTime, Long endTime) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        if (ValidateUtil.isNull(city)) {
            return AjaxResult.errorResult("市不能为空");
        }

        if (User.notHasCityAuth(user.getUsername(), city, user.getUserType())) {
            return AjaxResult.errorResult("您没有访问该城市数据的权限");
        }

        if (null == isLock) {
            isLock = 2;//0未锁定  1锁定  2全部
        }
        if (null != isVerify && 0 == -1 - isVerify) {
            isVerify = null;
        }
        if (!ValidateUtil.isAbsLong(shopId)) {
            shopId = null;
        }
        if (!ValidateUtil.isAbsInteger(type)) {
            type = null;
        }

        List<CityPostman> list = CityPostmanMemory.getInstance().listOfCity(city, isLock, isVerify, username, startTime, endTime, shopId, type);

        if (null != list && null != list && ValidateUtil.isAbsList(list)) {
            List<CityPostmanVO> voList = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                if (null == list.get(i)) {
                    continue;
                }
                CityPostman cityPostman = (CityPostman) list.get(i);
                if (null == cityPostman) {
                    continue;
                }
                CityPostmanVO vo = addOne(cityPostman);
                if (null != vo) {
                    voList.add(vo);
                }
            }
            String downloadUrl = laokepostManExcel(voList);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
        }
        return AjaxResult.successResult(0, 0, null);
    }

    //获取一条
    private CityPostmanVO addOne(CityPostman cityPostman) {
        if (null == cityPostman) {
            return null;
        }
        if (ValidateUtil.isNull(cityPostman.getUsername())) {
            return null;
        }
        UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(cityPostman.getUsername());
        if (null == userInfo) {
            return null;
        }
        CityPostmanVO cityPostmanVo = new CityPostmanVO();
        try {
            ObjectUtils.copyPropertiesExclude(userInfo, cityPostmanVo, null);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        cityPostmanVo.setVerifyTime(cityPostman.getVerifyTime());
        cityPostmanVo.setIsVerify(cityPostman.getIsVerify());
        cityPostmanVo.setIsWork(cityPostman.getIsWork());
        cityPostmanVo.setId(cityPostman.getId());
        cityPostmanVo.setIsLock(cityPostman.getIsLock());
        cityPostmanVo.setProvince(cityPostman.getProvince());
        cityPostmanVo.setCity(cityPostman.getCity());
        cityPostmanVo.setCounty(cityPostman.getCounty());

        if (ValidateUtil.isNotNull(cityPostmanVo.getSignPhoto()) && ValidateUtil.isNotNetUrl(cityPostmanVo.getSignPhoto())) {
            cityPostmanVo.setSignPhoto(SYS_CONFIG_PATH.IMAGE_BASE_URL + cityPostmanVo.getSignPhoto());
        }

        return cityPostmanVo;
    }

    //---------------------------------生成Excel
    private String laokepostManExcel(List<CityPostmanVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
//        String basePath = Common.proImgPath;
//            String basePath = "D:\\nginx-1.13.5\\html\\dw5fz\\wallet\\pop";
//            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
//            Date date = new Date(System.currentTimeMillis());
//            String fileHash = formatter.format(date);

//        String templatePath = basePath + File.separator + "a.xls";
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

//        File templateFile = new File(templatePath);
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行

            sheet.addCell(new Label(col++, row, "姓名"));
            sheet.addCell(new Label(col++, row, "登录账号"));
            sheet.addCell(new Label(col++, row, "手机号"));

            sheet.addCell(new Label(col++, row, "性别"));
            sheet.addCell(new Label(col++, row, "城市"));
            sheet.addCell(new Label(col++, row, "区县"));
            sheet.addCell(new Label(col++, row, "是否锁定"));

            row = 1;
            for (CityPostmanVO record : list) {
                col = 0;

                String isLock = "";
                if (record.getIsLock() == 1) {
                    isLock = "是";
                } else {
                    isLock = "否";
                }
                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getUsername()));
                sheet.addCell(new Label(col++, row, record.getTelPhone()));
                sheet.addCell(new Label(col++, row, record.getSex()));
                sheet.addCell(new Label(col++, row, record.getCity()));
                sheet.addCell(new Label(col++, row, record.getCounty()));
                sheet.addCell(new Label(col++, row, isLock));

                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }


    //查询所有已交押金的用户(带城市检索)-===导出excel
    @RequestMapping(method = RequestMethod.POST, value = "/admin/wallet/deposit/list/V2/export")
    @ResponseBody
    public String user_wallet__admin_V2_export(String token, String username, String province, String county, String city, Long startTime, Long endTime, Integer userType) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //验证是否有登录权限
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }

        Long totalCount = 0l;

        String timeStr = "";
        if (null != startTime && null != endTime) {
            timeStr = " and a.createTime >= " + startTime + " and a.createTime <= " + endTime;
        }

        StringBuffer sb = new StringBuffer();
        sb.append(" FROM ").append(PojoTables.USER_WALLET).append(" a ")
                .append(" left join ").append(PojoTables.CityPostman).append(" b on a.username = b.username")
                .append(" where b.delFlag = 0 and a.delFlag =0 ").append(timeStr);

        if (ValidateUtil.isNotNull(username)) {
            sb.append(" and a.username ='").append(username).append("'");
        }
        if (ValidateUtil.isNotNull(province)) {
            sb.append(" and b.province ='").append(province).append("'");
        }
        if (ValidateUtil.isNotNull(city)) {
            sb.append(" and b.city ='").append(city).append("'");
        }
        if (ValidateUtil.isNotNull(county)) {
            sb.append(" and b.county ='").append(county).append("'");
        }
//        if (null != userType) {
//            sb.append(" and b.userType= ").append(userType);
//        }

        try {
            StringBuffer sbCount = new StringBuffer();
            sbCount.append("select count(1) counts ");
            sbCount.append(sb.toString());
            totalCount = UserDaoSql.getInstance().getCount(sbCount.toString());
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("查询失败，请稍后再试");
        }

        List<?> list = null;
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT a.*, b.province ,b.city ,b.county ");
            sbSql.append(sb.toString());
            list = UserDaoSql.getInstance().list(UserWalletVO.class, sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult("查询失败，请稍后再试");
        }
        List<UserWalletVO> voList = new ArrayList<>();
        if (ValidateUtil.isAbsList(list)) {
            for (int i = 0; i < list.size(); i++) {
                UserWalletVO vo = (UserWalletVO) list.get(i);
                if (null == vo) {
                    continue;
                }
                if (ValidateUtil.isNull(vo.getUsername())) {
                    continue;
                }

                UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(vo.getUsername());
                if (null == userInfo) {
                    continue;
                }

                try {
                    ObjectUtils.copyPropertiesExclude(list.get(i), vo, null);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

                vo.setName(userInfo.getName());
                vo.setImageUrl(userInfo.getSignPhoto());
                voList.add(vo);
            }

            String downloadUrl = depositExcel(voList);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
//            return AjaxResult.successResult(totalCount, voList.size(), voList);
        }

        return AjaxResult.successResult(0, 0, null);
    }


    //---------------------------------生成Excel
    private String depositExcel(List<UserWalletVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行

            sheet.addCell(new Label(col++, row, "账号"));
            sheet.addCell(new Label(col++, row, "姓名"));
            sheet.addCell(new Label(col++, row, "手机号"));

            row = 1;
            for (UserWalletVO record : list) {
                col = 0;

                sheet.addCell(new Label(col++, row, record.getUsername()));
                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getDepositMoney() + ""));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return outputPath;
    }



// ======================================================

    // 考勤导excel
    private String SignInExcel(List<UserSignInfoVO> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行

            sheet.addCell(new Label(col++, row, "日期"));
            sheet.addCell(new Label(col++, row, "用户名"));
            sheet.addCell(new Label(col++, row, "上午上班"));
            sheet.addCell(new Label(col++, row, "上午下班"));
            sheet.addCell(new Label(col++, row, "下午上班"));
            sheet.addCell(new Label(col++, row, "下午下班"));

            row = 1;
            for (UserSignInfoVO record : list) {
                col = 0;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                String time = "";
                String time1 = "";
                String time2 = "";
                String time3 = "";
                if (null != record.getMorningSingnInTime()) {
                    time = sdf.format(record.getMorningSingnInTime()); // 上午上班
                }
                if (null != record.getMorningSingnOutTime()) {
                    time1 = sdf.format(record.getMorningSingnOutTime()); // 下班
                }
                if (null != record.getNoonSignInTime()) {
                    time2 = sdf.format(record.getNoonSignInTime()); // 下午上班
                }
                if (null != record.getNoonSignOutTime()) {
                    time3 = sdf.format(record.getNoonSignOutTime()); // 下班
                }

                sheet.addCell(new Label(col++, row, record.getDate()));
                sheet.addCell(new Label(col++, row, record.getName().equals("") ? record.getUsername() : record.getName() + "(" + record.getUsername() + ")"));
//                sheet.addCell(new Label(col++, row, record.getName() + "(" + record.getUsername() + ")"));
                sheet.addCell(new Label(col++, row, time));
                sheet.addCell(new Label(col++, row, time1));
                sheet.addCell(new Label(col++, row, time2));
                sheet.addCell(new Label(col++, row, time3));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

}
